﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.IO;

namespace Event_SG_EWDT
{
    public class DBMANAGER
    {
        //still need retrieve all members
        #region Member
        public static string CreateMember(Member m)//ok
        {
            SqlConnection conn = null;
            string newString = "";

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Member(name, handphone, email, interest, visibility) VALUES (@name, @handphone, @email, @interest, @visibility)";
                comm.Parameters.AddWithValue("@name", m.Name);
                comm.Parameters.AddWithValue("@handphone", m.Handphone);
                comm.Parameters.AddWithValue("@email", m.Email);
                comm.Parameters.AddWithValue("@interest", m.interest);
                comm.Parameters.AddWithValue("@visibility", 1);

                comm.Connection = conn;
          
                int addRow = (int)comm.ExecuteNonQuery();

                comm.CommandText = "Select @@identity as membership_no";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["membership_no"].ToString();
                dr.Close();

            }
                
               
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return newString;
        }

        public static Member RetrieveMember(String memid)//ok
        {
            SqlConnection conn = new SqlConnection();
            Member m = new Member();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member WHERE membership_no=@membership_no";
                    comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(memid));

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    m.MembershipNo = memid;
                    m.Name = dr["name"].ToString();
                    m.Handphone = dr["handphone"].ToString();
                    m.Email = dr["email"].ToString();
                    m.interest = Convert.ToInt32(dr["interest"]);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return m;
        }

        public static String RetrieveMemberByAccount(String acc)//ok
        {
            SqlConnection conn = new SqlConnection();
            String s = "";
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select membership_no from Account WHERE account=@account";
                comm.Parameters.AddWithValue("@account", acc);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    s = dr["membership_no"].ToString();
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return s;
        }

        public static bool UpdateMember(Member m)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member SET name=@name, handphone=@handphone, email=@email, interest=@interest WHERE membership_no=@membership_no";
                comm.Parameters.AddWithValue("@name", m.Name);
                comm.Parameters.AddWithValue("@handphone", m.Handphone);
                comm.Parameters.AddWithValue("@email", m.Email);
                comm.Parameters.AddWithValue("membership_no", m.MembershipNo);
                comm.Parameters.AddWithValue("@interest", m.interest);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static bool UpdateMember(Member m, string visibility)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member SET name=@name, handphone=@handphone, email=@email, interest=@interest, visibility=@visibility WHERE membership_no=@membership_no";
                comm.Parameters.AddWithValue("@name", m.Name);
                comm.Parameters.AddWithValue("@handphone", m.Handphone);
                comm.Parameters.AddWithValue("@email", m.Email);
                comm.Parameters.AddWithValue("membership_no", m.MembershipNo);
                comm.Parameters.AddWithValue("@interest", m.interest);
                comm.Parameters.AddWithValue("@visibility", visibility);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static bool DeleteMember(Member m)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete Member where membership_no = @membership_no";
                comm.Parameters.AddWithValue("@membership_no", m.MembershipNo);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        #endregion 

        #region Category
        public static List<Category> GetCategory(int Cid)//ok
        {
            List<Category> list = new List<Category>();

            SqlConnection conn = null;
            
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Category where category_id = @id";

                comm.Parameters.AddWithValue("@id", Cid);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Category category = new Category();
                    category.CategoryId = dr["category_id"].ToString();
                    category.CategoryName = (string)dr["category_name"];

                    list.Add(category);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        public static List<Category> RetrieveCategory()//ok
        {
            List<Category> list = new List<Category>();

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from category";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Category category = new Category();
                    category.CategoryId = dr[0].ToString();
                    category.CategoryName = dr[1].ToString();

                    list.Add(category);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        #endregion

        #region GroupType
        public static string GetGroupType(int groupTypeID)//ok
        {
            List<GroupType> list = new List<GroupType>();
            string s = "";
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select group_type_name from group_type where group_type_id like @group_id_id";

                comm.Parameters.AddWithValue("@group_id_id", groupTypeID);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    s = dr[0].ToString();
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return s;
        }
        public static List<GroupType> RetrieveGroupType()//ok
        {
            List<GroupType> a = new List<GroupType>();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from group_type";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    GroupType gt = new GroupType();
                    gt.GroupTypeId = dr[0].ToString();
                    gt.GroupTypeName = dr[1].ToString();
                    a.Add(gt);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return a;
        }
        
        #endregion

        #region Event
        public static string CreateEvent(Event e)//ok
        {
            SqlConnection conn = null;
            string newString = "";

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Event(event_title, description, start_time, end_time, event_fee, privacy, category_id, location, visibility, organizer) " +
                    "VALUES (@event_title, @description, @start_time, @end_time, @event_fee, @privacy, @category_id, @location, @visibility, @organizer)";
                comm.Parameters.AddWithValue("@event_title", e.EventTitle);
                comm.Parameters.AddWithValue("@description", e.Description);
                comm.Parameters.AddWithValue("@start_time", e.StartTime);
                comm.Parameters.AddWithValue("@end_time", e.EndTime);
                comm.Parameters.AddWithValue("@event_fee", e.EventFee);
                comm.Parameters.AddWithValue("@privacy", e.Privacy);
                comm.Parameters.AddWithValue("@category_id", e.CategoryId);
                comm.Parameters.AddWithValue("@location", e.Location);
                comm.Parameters.AddWithValue("@visibility", 1);
                comm.Parameters.AddWithValue("@organizer", e.Organiser);

                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();

                comm.CommandText = "Select @@identity as event_id";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["event_id"].ToString();
                dr.Close();

            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                return "";
            }
            finally
            {
                conn.Close();
            }

            return newString;
        }

        public static List<Event> RetrieveEvent()//ok
        {
            List<Event> list = new List<Event>();

            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Event";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Event e = new Event();
                    e.EventId = dr["event_id"].ToString();
                    e.EventTitle = dr["event_title"].ToString();
                    e.Description = dr["description"].ToString();
                    e.StartTime = (DateTime)dr["start_time"];
                    e.EndTime = (DateTime)dr["end_time"];
                    e.EventFee = Convert.ToDouble(dr["event_fee"]);
                    e.CategoryId = (int)dr["category_id"];
                    e.Privacy = (int)dr["privacy"];
                    e.Location = dr["location"].ToString();

                    list.Add(e);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }

        //fixed by yh....
        public static Event RetrieveEvent(string eventID)//ok
        {
         //   List<Event> list = new List<Event>();
            Event e = null;
            SqlConnection conn = new SqlConnection();

            try
            {
                e = new Event();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Event where event_id = @eventID";

                comm.Parameters.AddWithValue("@eventID", Convert.ToInt32(eventID));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    e.EventId = dr["event_id"].ToString();
                    e.EventTitle = dr["event_title"].ToString();
                    e.Description = dr["description"].ToString();
                    e.StartTime = (DateTime)dr["start_time"];
                    e.EndTime = (DateTime)dr["end_time"];
                    e.EventFee = Convert.ToDouble(dr["event_fee"]);
                    e.CategoryId = (int)dr["category_id"];
                    e.Privacy = (int)dr["privacy"];
                    e.Location = dr["location"].ToString();
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return e;
        }
        public static bool UpdateEvent(Event e)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Event set event_title=@event_title, description=@description, start_time=@start_time, " +
                    "end_time=@end_time, event_fee=@event_fee, privacy=@privacy, category_id=@category_id, location=@location WHERE event_id=@event_id";

                comm.Parameters.AddWithValue("@event_title", e.EventTitle);
                comm.Parameters.AddWithValue("@description", e.Description);
                comm.Parameters.AddWithValue("@start_time", e.StartTime);
                comm.Parameters.AddWithValue("@end_time", e.EndTime);
                comm.Parameters.AddWithValue("@event_fee", e.EventFee);
                comm.Parameters.AddWithValue("@privacy", e.Privacy);
                comm.Parameters.AddWithValue("@category_id", e.CategoryId);
                comm.Parameters.AddWithValue("@event_id", e.EventId);
                comm.Parameters.AddWithValue("@location", e.Location);
                //comm.Parameters.AddWithValue("@organizer", e.Organiser);

                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        public static bool UpdateEvent(Event e, string visibility)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Event set event_title=@event_title, description=@description, start_time=@start_time, " +
                    "end_time=@end_time, event_fee=@event_fee, privacy=@privacy, category_id=@category_id, location=@location, organizer=@organizer, visibility=@visibility WHERE event_id=@event_id";

                comm.Parameters.AddWithValue("@event_title", e.EventTitle);
                comm.Parameters.AddWithValue("@description", e.Description);
                comm.Parameters.AddWithValue("@start_time", e.StartTime);
                comm.Parameters.AddWithValue("@end_time", e.EndTime);
                comm.Parameters.AddWithValue("@event_fee", e.EventFee);
                comm.Parameters.AddWithValue("@privacy", e.Privacy);
                comm.Parameters.AddWithValue("@category_id", e.CategoryId);
                comm.Parameters.AddWithValue("@event_id", e.EventId);
                comm.Parameters.AddWithValue("@location", e.Location);
                comm.Parameters.AddWithValue("@organizer", e.Organiser);
                comm.Parameters.AddWithValue("@visibility", visibility);

                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        #endregion

        /*#region Location
        public static bool AddLocation(Event e, string location)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Location (event_id, address) VALUES (@event_id, @address)";

                comm.Parameters.AddWithValue("@event_id", e.EventId);
                comm.Parameters.AddWithValue("@address", location);
               
                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();
                if (addRow == 1)
                {
                    return true;
                }
            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return false;
        }

      

        public static bool UpdateLocation(Event e, string location)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Location set address=@address WHERE event_id=@event_id";

                comm.Parameters.AddWithValue("@event_id", e.EventId);
                comm.Parameters.AddWithValue("@address", location);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        #endregion*/

        #region MemberEvent
        public static bool AddMemberEvent(MemberEvent me)//ok
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Member_Event(membership_no, event_id, member_role, status, visibility) VALUES (@membership_no, @event_id, @member_role, @status, @visibility)";
                comm.Parameters.AddWithValue("@membership_no",Convert.ToInt32(me.MembershipNo));
                comm.Parameters.AddWithValue("@event_id", Convert.ToInt32(me.EventId));
                comm.Parameters.AddWithValue("@member_role", me.MemberRole);
                comm.Parameters.AddWithValue("@status", me.Status);
                comm.Parameters.AddWithValue("@visibility", 1);

                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();

                if (addRow == 1)
                {
                    return true;
                }
            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
            finally
            {
                conn.Close();
            }

            return false;
        }

        public static List<MemberEvent> RetrieveMemberEvent(String memid)//ok
        {
            SqlConnection conn = new SqlConnection();
            List<MemberEvent> list = new List<MemberEvent>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Event WHERE membership_no=@membership_no";
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(memid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MemberEvent me = new MemberEvent();
                    me.MembershipNo = memid;
                    me.EventId = dr["event_id"].ToString();
                    me.MemberRole = dr["member_role"].ToString();
                    me.Status = (int)dr["status"];
                    list.Add(me);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        public static List<MemberEvent> RetrieveMemberEvent(String memid, string eventid)//ok
        {
            SqlConnection conn = new SqlConnection();
            List<MemberEvent> list = new List<MemberEvent>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Event WHERE membership_no=@membership_no and event_id=@event_id";
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(memid));
                comm.Parameters.AddWithValue("@event_id", Convert.ToInt32(eventid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MemberEvent me = new MemberEvent();
                    me.MembershipNo = memid;
                    me.EventId = dr["event_id"].ToString();
                    me.MemberRole = dr["member_role"].ToString();
                    me.Status = (int)dr["status"];
                    list.Add(me);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        public static int RetrieveMemberEvent_Status(String memid, String eventid)//ok
        {
            SqlConnection conn = new SqlConnection();
            List<MemberEvent> list = new List<MemberEvent>();
            int Status = -1;
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Event WHERE membership_no=@membership_no and event_id = @event_no";
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(memid));
                comm.Parameters.AddWithValue("@event_no",Convert.ToInt32(eventid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    Status = (int)dr["status"];

                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return Status;
        }
        public static int getAdminForEvent(string eventid)
        {
            SqlConnection conn = new SqlConnection();
            int i= 0;

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Event WHERE event_id= @event_id and member_role like 'Admin' ";

                comm.Parameters.AddWithValue("@event_id", Convert.ToInt32(eventid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //MemberEvent me = new MemberEvent();
                    //me.MembershipNo = dr[0].ToString();
                    //me.EventId = dr["event_id"].ToString();
                    //me.MemberRole = dr["member_role"].ToString();
                    //me.Status = (int)dr["status"];
                    i = Convert.ToInt32(dr[0]);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return i;

        }

        public static bool UpdateMemberEvent(MemberEvent me)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member_Event SET member_role=@member_role, status=@status WHERE membership_no=@membership_no and event_id=@event_id";
                comm.Parameters.AddWithValue("@membership_no", me.MembershipNo);
                comm.Parameters.AddWithValue("@member_role", me.MemberRole);
                comm.Parameters.AddWithValue("@event_id", me.EventId);
                comm.Parameters.AddWithValue("@status", me.Status);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static bool UpdateMemberEvent_status(MemberEvent me)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member_Event SET status=@status WHERE membership_no=@membership_no and event_id=@event_id";
                comm.Parameters.AddWithValue("@membership_no", me.MembershipNo);
                comm.Parameters.AddWithValue("@event_id", me.EventId);
                comm.Parameters.AddWithValue("@status", me.Status);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static bool UpdateMemberEvent_Role(MemberEvent me)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member_Event SET member_role=@member_role WHERE membership_no=@membership_no and event_id=@event_id";
                comm.Parameters.AddWithValue("@membership_no", me.MembershipNo);
                comm.Parameters.AddWithValue("@event_id", me.EventId);
                comm.Parameters.AddWithValue("@member_role", me.MemberRole);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static bool UpdateMemberEvent(MemberEvent me, string visibility)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member_Event SET member_role=@member_role, status=@status, visibility=@visibility WHERE membership_no=@membership_no and event_id=@event_id";
                comm.Parameters.AddWithValue("@membership_no", me.MembershipNo);
                comm.Parameters.AddWithValue("@member_role", me.MemberRole);
                comm.Parameters.AddWithValue("@event_id", me.EventId);
                comm.Parameters.AddWithValue("@status", me.Status);
                comm.Parameters.AddWithValue("@visibility", visibility);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        public static bool DeleteMemberEvent(MemberEvent me)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Member_Event WHERE membership_no=@membership_no and event_id=@event_id";

                comm.Parameters.AddWithValue("@membership_no", me.MembershipNo);
                comm.Parameters.AddWithValue("@event_id", me.EventId);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        #endregion

        #region Group
        public static string CreateGroup(Group g)//ok
        {
            SqlConnection conn = null;
            string newString = "";

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Grouping (group_name, description, group_privacy, group_type_id, visibility) " +
                    "VALUES (@group_name, @description, @group_privacy, @group_type_id, @visibility)";
            
                comm.Parameters.AddWithValue("@group_name", g.GroupName);
                comm.Parameters.AddWithValue("@description", g.Description);
                comm.Parameters.AddWithValue("@group_privacy", g.GroupPrivacy);
                comm.Parameters.AddWithValue("@group_type_id", g.GroupTypeId);
                comm.Parameters.AddWithValue("@visibility", 1);
        
                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();

                comm.CommandText = "Select @@identity as group_id";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["group_id"].ToString();
                dr.Close();

            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return newString;
        }

        public static List<Group> GetAllGroup()//ok
        {
            List<Group> list = new List<Group>();

            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Grouping";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Group g = new Group();
                    g.GroupId = dr["group_id"].ToString();
                    g.GroupName = dr["group_name"].ToString();
                    g.Description = dr["description"].ToString();
                    g.GroupTypeId = dr["group_type_id"].ToString();
                    g.GroupPrivacy = (int)dr["group_privacy"];

                    list.Add(g);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        public static Group GetAGroup(string groupid)//ok
        {
            //FIXED by yh

            //List<Group> list = new List<Group>(); //?
            Group g = null;
            SqlConnection conn = new SqlConnection();
            try
            {
                g = new Group();

                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Grouping where group_id = @groupid";
                comm.Parameters.AddWithValue("@groupid",Convert.ToInt32(groupid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    g.GroupId = dr["group_id"].ToString();
                    g.GroupName = dr["group_name"].ToString();
                    g.Description = dr["description"].ToString();
                    g.GroupTypeId = dr["group_type_id"].ToString();
                    g.GroupPrivacy = (int)dr["group_privacy"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                return null;
                //throw ex;
            }
            finally
            {
                conn.Close();
            }
            return g;
        }
        public static int GetNumberOfMember(string groupid)//ok
        {
            int i = -1;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select count(*) from member_group where group_id = @groupid and status =1";
                comm.Parameters.AddWithValue("@groupid", Convert.ToInt32(groupid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                  i = Convert.ToInt32 (dr[0]);

                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return i;
        }
        public static bool UpdateGroup(Group g)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Grouping set group_name=@group_name, description=@description, group_privacy=@group_privacy, group_type_id=@group_type_id " +
                    "WHERE group_id=@group_id";

                comm.Parameters.AddWithValue("@group_name", g.GroupName);
                comm.Parameters.AddWithValue("@description", g.Description);
                comm.Parameters.AddWithValue("@group_privacy", g.GroupPrivacy);
                comm.Parameters.AddWithValue("@group_type_id", Convert.ToInt32(g.GroupTypeId));
                comm.Parameters.AddWithValue("@group_id", g.GroupId);

                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        public static bool UpdateGroup(Group g, string visibility)//ok
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Grouping set group_name=@group_name, description=@description, group_privacy=@group_privacy, group_type_id=@group_type_id, visibility=@visibility " +
                    "WHERE group_id=@group_id";

                comm.Parameters.AddWithValue("@group_name", g.GroupName);
                comm.Parameters.AddWithValue("@description", g.Description);
                comm.Parameters.AddWithValue("@group_privacy", g.GroupPrivacy);
                comm.Parameters.AddWithValue("@group_type_id", g.GroupTypeId);
                comm.Parameters.AddWithValue("@group_id", g.GroupId);
                comm.Parameters.AddWithValue("@visibility", visibility);

                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        #endregion

        #region MemberGroup
        public static bool AddMemberGroup(MemberGroup mg)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Member_Group(membership_no, group_id, member_type, status, visibility) VALUES (@membership_no, @group_id, @member_type, @status, @visibility)";
                
                comm.Parameters.AddWithValue("@membership_no",Convert.ToInt32(mg.MembershipNo));
                comm.Parameters.AddWithValue("@group_id", Convert.ToInt32(mg.GroupId));
                comm.Parameters.AddWithValue("@member_type", mg.MemberType);
                comm.Parameters.AddWithValue("@status", mg.Status);
                comm.Parameters.AddWithValue("@visibility", 1);

                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();

                if (addRow == 1)
                {
                    return true;
                }
            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return false;
        }

        public static List<MemberGroup> RetrieveMemberGroupByMemberID(String memid)
        {
            SqlConnection conn = new SqlConnection();
            List<MemberGroup> list = new List<MemberGroup>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Group WHERE membership_no=@membership_no";
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(memid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MemberGroup mg = new MemberGroup();
                    mg.MembershipNo = memid;
                    mg.GroupId = dr["group_id"].ToString();
                    mg.MemberType = dr["member_type"].ToString();
                    mg.Status = (int)dr["status"];

                    list.Add(mg);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        public static List<MemberGroup> RetrieveMemberGroupByGroupid(String Groupid)
        {
            SqlConnection conn = new SqlConnection();
            List<MemberGroup> list = new List<MemberGroup>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Group WHERE group_id=@group_id";
                comm.Parameters.AddWithValue("@group_id", Convert.ToInt32(Groupid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MemberGroup mg = new MemberGroup();
                    mg.MembershipNo = dr["membership_no"].ToString();
                    mg.GroupId = Groupid;
                    mg.MemberType = dr["member_type"].ToString();
                    mg.Status = (int)dr["status"];

                    list.Add(mg);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        public static List<MemberGroup> RetrieveAMemberGroupinList(String Groupid, string memberid)
        {
            SqlConnection conn = new SqlConnection();
            List<MemberGroup> list = new List<MemberGroup>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Group WHERE group_id=@Groupid and membership_no= @membership_no";
                comm.Parameters.AddWithValue("@Groupid", Convert.ToInt32(Groupid));
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(memberid));

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MemberGroup mg = new MemberGroup();
                    mg.MembershipNo = Groupid;
                    mg.GroupId = dr["group_id"].ToString();
                    mg.MemberType = dr["member_type"].ToString();
                    mg.Status = (int)dr["status"];

                    list.Add(mg);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        
        public static bool UpdateMemberGroup_status(MemberGroup mg)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member_Group SET  status=@status WHERE membership_no=@membership_no and group_id=@group_id";

                comm.Parameters.AddWithValue("@membership_no", mg.MembershipNo);
                comm.Parameters.AddWithValue("@group_id", mg.GroupId);
                comm.Parameters.AddWithValue("@status", mg.Status);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static bool UpdateMemberGroup_Membertype(MemberGroup mg)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member_Group SET member_type=@member_type WHERE membership_no=@membership_no and group_id=@group_id";

                comm.Parameters.AddWithValue("@membership_no", mg.MembershipNo);
                comm.Parameters.AddWithValue("@group_id", mg.GroupId);
                comm.Parameters.AddWithValue("@member_type", mg.MemberType);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        public static bool DeleteMemberGroup(MemberGroup mg)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Member_Group WHERE membership_no=@membership_no and group_id=@group_id";

                comm.Parameters.AddWithValue("@membership_no", mg.MembershipNo);
                comm.Parameters.AddWithValue("@group_id", mg.GroupId);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        public static int getAdminForGroup(string groupid)
        {
            SqlConnection conn = new SqlConnection();
            int i = 0;

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Member_Group WHERE group_id= @group_id and member_type like 'Admin' ";

                comm.Parameters.AddWithValue("@group_id", Convert.ToInt32(groupid));
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //MemberEvent me = new MemberEvent();
                    //me.MembershipNo = dr[0].ToString();
                    //me.EventId = dr["event_id"].ToString();
                    //me.MemberRole = dr["member_role"].ToString();
                    //me.Status = (int)dr["status"];
                    i = Convert.ToInt32(dr[0]);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return i;

        }

        #endregion

        #region Account
        public static bool CreateAccount(Account a)//ok
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Account(account, password, membership_no, visibility) VALUES (@account, @password, @membership_no, @visibility)";
                comm.Parameters.AddWithValue("@account", a.Account_Name);
                comm.Parameters.AddWithValue("@password", a.Password);
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(a.Membership_No));
                comm.Parameters.AddWithValue("@visibility", 1);

                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();
                if (addRow == 1)
                {
                    return true;
                }

            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return false;
        }

        public static bool RetrieveAccount(String account, String password) //ok //hash password later
        {
            SqlConnection conn = new SqlConnection();
            Account a = new Account();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Account WHERE account=@account AND password=@password";
                comm.Parameters.AddWithValue("@account", account);
                comm.Parameters.AddWithValue("@password", password);
                SqlDataReader dr = comm.ExecuteReader();
               
                while (dr.Read())
                {
                    if(dr["account"] != null && dr["password"] != null)
                    {
                        return true;
                    }
                }
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static string RetrieveAccount(string memberID) //ok //hash password later
        {
            SqlConnection conn = new SqlConnection();
            Account a = new Account();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select account from Account where membership_no=@membership_no";
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32( memberID));
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                        return dr[0].ToString();
                }
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return "";
        }

        public static bool UpdatePassword(Account a)//ok
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE Account set password=@password WHERE account=@account";

                comm.Parameters.AddWithValue("@account", a.Account_Name);
                comm.Parameters.AddWithValue("@password", a.Password);
                comm.Parameters.AddWithValue("@membership_no", a.Membership_No);

                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();
                if (addRow == 1)
                {
                    return true;
                }

            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return false;
        }

        public static string RetrievePassword(string memberid)
        {
            SqlConnection conn = null;
            string pass = "";
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "select password from account where membership_no=@membership_no";
                comm.Parameters.AddWithValue("@membership_no",Convert.ToInt32(memberid));
                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    pass = dr["password"].ToString();
                }
                dr.Close();

            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return pass;
        }

        #endregion

        #region Image
        public static string UploadImage(byte[] b)
        {

            SqlConnection conn = new SqlConnection();
            string newString = "";
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "insert into image (image) values (@image)";
                comm.Parameters.AddWithValue("@image", b);
                int rowsAffected = comm.ExecuteNonQuery();

                comm.CommandText = "Select @@identity as image_id";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["image_id"].ToString();
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newString;
        }

        public static string GetImageIdEventPoster(string id)
        {
            SqlConnection conn = new SqlConnection();
            string newString = "";
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select image_poster from Event where event_id=@event_id";
                comm.Parameters.AddWithValue("@event_id", Convert.ToInt32(id));
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["image_poster"].ToString();
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newString;
        }

        public static string GetImageIdEventThumbnail(string id)
        {
            SqlConnection conn = new SqlConnection();
            string newString = "";
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select image_thumb from Event where event_id=@event_id";
                comm.Parameters.AddWithValue("@event_id", Convert.ToInt32(id));
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["image_thumb"].ToString();
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newString;
        }
        public static bool SetEventImagePoster(string eventid, string imageid)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Event set image_poster=@image_poster where event_id=@event_id";
                comm.Parameters.AddWithValue("@image_poster", Convert.ToInt32(imageid));
                comm.Parameters.AddWithValue("@event_id", Convert.ToInt32(eventid));


                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        

        public static bool SetEventImageThumbnail(string eventid, string imageid)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Event set image_thumb=@image_thumb where event_id=@event_id";
                comm.Parameters.AddWithValue("@image_thumb", Convert.ToInt32(imageid));
                comm.Parameters.AddWithValue("@event_id", Convert.ToInt32(eventid));


                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static string GetImageIdGroupLogo(string id)
        {
            SqlConnection conn = new SqlConnection();
            string newString = "";
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select image_logo from Grouping where group_id=@group_id";
                comm.Parameters.AddWithValue("@group_id", Convert.ToInt32(id));
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["image_logo"].ToString();
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newString;
        }

        public static string GetImageIdGroupThumbnail(string id)
        {
            SqlConnection conn = new SqlConnection();
            string newString = "";
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select image_thumb from Grouping where group_id=@group_id";
                comm.Parameters.AddWithValue("@group_id", Convert.ToInt32(id));
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["image_thumb"].ToString();
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newString;
        }
        public static bool SetGroupImageLogo(string groupid, string imageid)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Grouping set image_logo=@image_logo where group_id=@group_id";
                comm.Parameters.AddWithValue("@image_logo", Convert.ToInt32(imageid));
                comm.Parameters.AddWithValue("@group_id", Convert.ToInt32(groupid));


                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }


        public static bool SetGroupImageThumbnail(string groupid, string imageid)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Grouping set image_thumb=@image_thumb where group_id=@group_id";
                comm.Parameters.AddWithValue("@image_thumb", Convert.ToInt32(imageid));
                comm.Parameters.AddWithValue("@group_id", Convert.ToInt32(groupid));


                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        #endregion



        #region Notification
        public static string CreateNotification(Notification n)//ok
        {
            SqlConnection conn = null;
            string newString = "";

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT into Notification (sender, receiver, notification, time, awareness) VALUES (@sender, @receiver, @notification, @time, @awareness)";
                comm.Parameters.AddWithValue("@sender",n.sender);
                comm.Parameters.AddWithValue("@receiver", n.receiver);
                comm.Parameters.AddWithValue("@notification", n.NotificationContent);
                comm.Parameters.AddWithValue("@time", n.time);
                comm.Parameters.AddWithValue("@awareness", 0);

                comm.Connection = conn;

                int addRow = (int)comm.ExecuteNonQuery();

                comm.CommandText = "Select @@identity as notice_id";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newString = dr["notice_id"].ToString();
                dr.Close();
            }


            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return newString;
        }
        public static List<Notification> RetrieveMyNotification(String receiver)
        {
            SqlConnection conn = new SqlConnection();
            List<Notification> tempDT = new List<Notification>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT TOP 100    notice_id , notification , sender , receiver , time , awareness FROM         Notification where receiver = @receiver order by 5";
                comm.Parameters.AddWithValue("@receiver", receiver);
                SqlDataReader dr = comm.ExecuteReader();

                

                while (dr.Read())
                {
                    Notification n = new Notification();

                    n.Notice_id = dr[0].ToString();
                    n.NotificationContent = dr[1].ToString();
                    n.sender = dr[2].ToString();
                    n.receiver = dr[3].ToString();

                    tempDT.Add(n);
                }
                dr.Close();
                return tempDT;
            }
            catch(Exception ex)
            {
                Console.Write(ex.Message);

            }
            return tempDT;
        }
        public static List<Member> RetrieveGroupMember_NotAdmin(String groupid)
        {
            SqlConnection conn = new SqlConnection();
            List<Member> tempDT = new List<Member>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member.* ,  member_group.member_type, status  FROM  Member INNER JOIN  Member_Group ON Member.membership_no = Member_Group.membership_no where status = 1 and  member_group.group_id = @groupid and member_type!='Admin'";
                comm.Parameters.AddWithValue("@groupid", Convert.ToInt32( groupid));
                SqlDataReader dr = comm.ExecuteReader();



                while (dr.Read())
                {
                    Member m = new Member();

                    m.MembershipNo = dr[0].ToString();
                    m.Name = dr[1].ToString();
                    m.Email = dr[3].ToString();

                    tempDT.Add(m);
                }
                dr.Close();
                return tempDT;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);

            }
            return tempDT;
        }
        public static List<Member> RetrieveGroupMember_OnlyAdmin(String groupid)
        {
            SqlConnection conn = new SqlConnection();
            List<Member> tempDT = new List<Member>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member.* ,  member_group.member_type, status  FROM  Member INNER JOIN  Member_Group ON Member.membership_no = Member_Group.membership_no where status = 1 and  member_group.group_id = @groupid and member_type=='Admin'";
                comm.Parameters.AddWithValue("@groupid",Convert.ToInt32(  groupid));
                SqlDataReader dr = comm.ExecuteReader();



                while (dr.Read())
                {
                    Member m = new Member();

                    m.MembershipNo = dr[0].ToString();
                    m.Name = dr[1].ToString();
                    m.Email = dr[3].ToString();

                    tempDT.Add(m);
                }
                dr.Close();
                return tempDT;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);

            }
            return tempDT;
        }


        public static List<Member> RetrieveEventMember_NotAdmin(String eventid)
        {
            SqlConnection conn = new SqlConnection();
            List<Member> tempDT = new List<Member>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member.* ,  member_event.member_role, status  FROM         Member INNER JOIN  Member_Event ON Member.membership_no = Member_Event.membership_no where status = 0 and  member_event.event_id = @eventid and member_role!='Admin'";
                comm.Parameters.AddWithValue("@eventid",Convert.ToInt32( eventid));
                SqlDataReader dr = comm.ExecuteReader();



                while (dr.Read())
                {
                    Member m = new Member();

                    m.MembershipNo = dr[0].ToString();
                    m.Name = dr[1].ToString();
                    m.Email = dr[3].ToString();

                    tempDT.Add(m);
                }
                dr.Close();
                return tempDT;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);

            }
            return tempDT;
        }
        public static List<Member> RetrieveEventMember_OnlyAdmin(String eventid)
        {
            SqlConnection conn = new SqlConnection();
            List<Member> tempDT = new List<Member>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member.* ,  member_event.member_role, status  FROM         Member INNER JOIN  Member_Event ON Member.membership_no = Member_Event.membership_no where status = 0 and  member_event.event_id = @eventid and member_role=='Admin'";
                comm.Parameters.AddWithValue("@eventid", Convert.ToInt32( eventid ));
                SqlDataReader dr = comm.ExecuteReader();



                while (dr.Read())
                {
                    Member m = new Member();

                    m.MembershipNo = dr[0].ToString();
                    m.Name = dr[1].ToString();
                    m.Email = dr[3].ToString();

                    tempDT.Add(m);
                }
                dr.Close();
                return tempDT;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);

            }
            return tempDT;
        }
        #endregion


    }
        
}